package com.apes.scm.masterdata.stock.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.party.model.PartyRole;
import com.apes.scm.masterdata.stock.model.Location;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-01-23 16:07
 */
public interface LocationRepository extends CustomRepository<Location, String> {
    @Query("SELECT T FROM Location T WHERE 1 = 1 and (T.id = :id or T.name like %:value%) ")
    Page<Location> find(@Param("id") String id, @Param("value") String value, Pageable pageable);

    @Query("select new map(l.id as id, l.name as name, l.company.id as companyId, l.company.party.name as companyName, " +
            " l.depot.id as depotId, l.depot.name as depotName, l.depot.dept.id as deptId, l.depot.dept.name as deptName," +
            " l.usage.id as usageId, l.usage.name as usageName," +
            " l.updateDate, to_char(l.lastUpdatedStamp, 'yyyy-mm-dd hh24:mi:ss.FF3') as lastUpdatedStamp ) " +
            " from Location l " +
            " where l.valid = true and l.lastUpdatedStamp > :times or (l.id > :id and l.lastUpdatedStamp = :times) " +
            " order by l.lastUpdatedStamp, l.id asc ")
    List<Map> findLocationByTs(@Param("times") Date times, @Param("id") String id, Pageable pageable);

    /**
     * fetch data by id and name and dept id
     *
     * @param id  id
     * @param value name
     * @param deptIds  dept id
     * @param pageable  page
     * @return Page<Location>
     */
    @Query("SELECT T FROM Location T   WHERE 1 = 1 and (T.id = :id or T.name like %:value%) and T.depot.dept.id in (:deptIds) ")
    Page<Location> findByBase(@Param("id") String id, @Param("value") String value, @Param("deptIds") List<String> deptIds, Pageable pageable);


    @Query(value = " SELECT * FROM V_CONSUMABLE_LOCATION A " +
            "  WHERE BUSINESS_TYPE_ID = :businessTypeId " +
            "    AND ((1 = :queryAll) OR (A.ID = :id OR A.NAME LIKE %:name%)) AND A.USAGE_ID = :usage AND A.COMPANY_ID = :companyId",
            countQuery=" SELECT count(*) FROM V_CONSUMABLE_LOCATION A " +
                    "  WHERE BUSINESS_TYPE_ID = :businessTypeId " +
                    "    AND ((1 = :queryAll) OR (A.ID = :id OR A.NAME LIKE %:name%)) AND A.USAGE_ID = :usage AND A.COMPANY_ID = :companyId ", nativeQuery = true)
    Page<Location> customizeConditionFind(@Param("queryAll")int queryAll, @Param("id")String id, @Param("name")String name, @Param("usage")String usageId, @Param("businessTypeId")String businessTypeId, @Param("companyId")String companyId, Pageable pageable);

    @Query(value = "select * from MAS_LOCATION t where t.DEPOT_ID = (SELECT b.id FROM MAS_DEPOT b WHERE b.DEPT_ID = :deptId and b.id = t.depot_id and rownum = 1) and t.USAGE_ID = '1' and t.VALID = 1",nativeQuery = true)
    List<Location> findByDept(@Param("deptId")String deptId);

    @Query(value = "select * from MAS_LOCATION t where t.DEPOT_ID in (SELECT t.id FROM MAS_DEPOT T WHERE T.DEPT_ID = :deptId) and t.USAGE_ID = '1' and t.VALID = 1",nativeQuery = true)
    List<Location> findLocationByDept(@Param("deptId")String deptId);

    @Query(value = "select * from MAS_LOCATION t where t.DEPOT_ID in (SELECT t.id FROM MAS_DEPOT T WHERE T.DEPT_ID = :deptId) and t.VALID = 1",nativeQuery = true)
    List<Location> findLocationsByDept(@Param("deptId")String deptId);


    @Query(value = "select * from MAS_LOCATION t where t.DEPOT_ID = (SELECT t.id FROM MAS_DEPOT T WHERE rownum = 1 and T.DEPT_ID = :deptId) and t.USAGE_ID = '4' and t.VALID = 1",nativeQuery = true)
    Location findByDeptFromApp(@Param("deptId")String deptId);

    @Query(value = "SELECT * from MAS_LOCATION t where t.company_id = :CompanyId AND t.VALID = 1 and t.USAGE_ID = '8'",nativeQuery = true)
    Location findlocationByCompany(@Param("CompanyId")String CompanyId);

    @Query(value = "SELECT * from MAS_LOCATION t where t.company_id = :CompanyId AND t.VALID = 1 and t.USAGE_ID = '8'",nativeQuery = true)
    List<Location> findLocation(@Param("CompanyId")String CompanyId);

    @Query(value = "" +
            "SELECT * " +
            "  FROM (SELECT ID C_LOCATION_ID, " +
            "                (SELECT DEPOT_LEVEL FROM MAS_DEPOT J WHERE J.ID = A.DEPOT_ID) DEPOT_LEVEL, " +
            "                (SELECT (SELECT ID FROM MAS_STORE K WHERE K.DEPT_ID = J.DEPT_ID) " +
            "                    FROM MAS_DEPOT J " +
            "                   WHERE J.ID = A.DEPOT_ID) STORE_ID " +
            "           FROM MAS_LOCATION A " +
            "          WHERE USAGE_ID = '1' AND A.ID IN ?1 ) " +
            " ORDER BY DEPOT_LEVEL ", nativeQuery = true)
    List<Map> safetyStockCalcuQuery(List locations);

    @Query(value = "" +
            "SELECT * " +
            "  FROM (SELECT ID, " +
            "                (SELECT DEPOT_LEVEL " +
            "                    FROM MAS_DEPOT J " +
            "                   WHERE J.ID = A.DEPOT_ID " +
            "                     AND DEPOT_LEVEL != '000122') DEPOT_LEVEL " +
            "           FROM MAS_LOCATION A) " +
            " WHERE DEPOT_LEVEL IS NOT NULL AND ID = ?1 ", nativeQuery = true)
    List<Map> stockCalcuLocationDevel(String locationId);

}
